package com.shuhe.common;

import com.cnpchse.sm2decrypt.SMutil;
import com.shuhe.JettyServer;
import com.shuhe.domain.mxgc.*;
import com.shuhe.metastore.DBSql;
import com.shuhe.metastore.MetaMysql;
import org.influxdb.InfluxDB;
import org.influxdb.dto.Pong;
import org.influxdb.dto.Query;
import org.influxdb.dto.QueryResult;
import org.quartz.CronExpression;

import java.sql.Connection;
import java.sql.ResultSet;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;

import static com.shuhe.common.CommonFunction.closeDb;
import static com.shuhe.handler.DataInHandler.strDateFormat;

public class Tools {

    //时间格式化,sj, 2024-01-01 00:00:01，需要调整
    public static String sjGsh(String gs, String sj) throws Exception {
        String sjgsh = "";
        if (gs.equals("yyyy-MM-dd")) {
            sjgsh = sj.substring(0, 10);
        }
        if (gs.equals("yyyy-MM-dd hh24:mi:ss")) {
            sjgsh = sj;
        }
        if (gs.equals("yyyyMMdd")) {
            sjgsh = sj.substring(0, 10).replace("-", "");
        }
        if (gs.equals("yyyyMMddhhmiss")) {
            sjgsh = sj.replace("-", "").replace(":", "").replace(" ", "");
        }
        if (gs.equals("yyyy/MM/dd")) {
            sjgsh = sj.substring(0, 10).replace("-", "/");
        }
        if (gs.equals("yyyy/MM/dd hh24:mi:ss")) {
            sjgsh = sj.replace("-", "/");
        }
        return sjgsh;
    }

    //日期转字符串
    public static String dateFormat(Date date) {
        String dateString = "";
        SimpleDateFormat nyrSfm = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        dateString = nyrSfm.format(date);
        return dateString;
    }

    // 任务串解析
    public static boolean cronIf(String runCron, String lastTime, String cTime) throws Exception {
        boolean cronIf = false;

        CronExpression cron = new CronExpression(runCron);
        Date nextExecutionTime = cron.getNextValidTimeAfter(strDateFormat(lastTime));
        //任务运行节点
        if (strDateFormat(cTime).compareTo(nextExecutionTime) >= 0) {
            cronIf = true;
        }
        return cronIf;
    }


    //获取连接url,className，参数为String
    public static HashMap<String, String> getConnect(String dbType, String dbIp, String dbPort, String dbName) throws Exception {
        MxUserSjyglResponseBody mxUserSjyglResponseBody = new MxUserSjyglResponseBody();
        mxUserSjyglResponseBody.setDbType(dbType);
        mxUserSjyglResponseBody.setIp(dbIp);
        mxUserSjyglResponseBody.setSourcePort(dbPort);
        mxUserSjyglResponseBody.setDbName(dbName);
        HashMap<String, String> hashMaps = getConnect(mxUserSjyglResponseBody);
        return hashMaps;

    }


    //获取连接url,className
    public static HashMap<String, String> getConnect(MxUserSjyglResponseBody mxUserSjyglResponseBody) throws Exception {
        HashMap<String, String> keyValueArray = new HashMap<>();
        String url = "";
        String className = "";
        String dbType = mxUserSjyglResponseBody.getDbType();
        switch (dbType) {
            case "mysql":
                url = "jdbc:mysql://".concat(mxUserSjyglResponseBody.getIp()).concat(":").concat(mxUserSjyglResponseBody.
                        getSourcePort()).concat("/").concat(mxUserSjyglResponseBody.getDbName()).concat(
                        "?characterEncoding=UTF-8");
                url = url + "&connectTimeout=50000&socketTimeout=50000";
                className = "com.mysql.jdbc.Driver";
                break;
            case "oracle":
                url = "jdbc:oracle:thin:@".concat(mxUserSjyglResponseBody.getIp()).concat(":").concat(mxUserSjyglResponseBody.
                        getSourcePort()).concat("/").concat(mxUserSjyglResponseBody.getDbName());
                className = "oracle.jdbc.OracleDriver";
                break;
            case "postgresql":
                url = "jdbc:postgresql://".concat(mxUserSjyglResponseBody.getIp()).concat(":").concat(mxUserSjyglResponseBody.
                        getSourcePort()).concat("/").concat(mxUserSjyglResponseBody.getDbName());
                className = "org.postgresql.Driver";
            case "gaussdb":
                url = "jdbc:postgresql://".concat(mxUserSjyglResponseBody.getIp()).concat(":").concat(mxUserSjyglResponseBody.
                        getSourcePort()).concat("/").concat(mxUserSjyglResponseBody.getDbName());
                className = "org.postgresql.Driver";
                break;
            case "sqlserver":
                url = "jdbc:sqlserver://".concat(mxUserSjyglResponseBody.getIp()).concat(":").concat(mxUserSjyglResponseBody.
                        getSourcePort()).concat(";DatabaseName=").concat(mxUserSjyglResponseBody.getDbName());
                className = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
                break;
            case "influxdb":
                url = "http://".concat(mxUserSjyglResponseBody.getIp()).concat(":").concat(mxUserSjyglResponseBody.getSourcePort()).
                        concat("/").concat(mxUserSjyglResponseBody.getDbName()).concat("/");
                break;
            case "dm":
                url = "jdbc:dm://" + mxUserSjyglResponseBody.getIp() + ":" + mxUserSjyglResponseBody.getSourcePort() + "/"
                        + mxUserSjyglResponseBody.getDbName();
                className = "dm.jdbc.driver.DmDriver";
                break;
            case "gbase":
                url = "jdbc:gbasedbt-sqli://" + mxUserSjyglResponseBody.getIp() + ":" + mxUserSjyglResponseBody.getSourcePort() + "/"
                        + mxUserSjyglResponseBody.getDbName();
                className = "com.gbasedbt.jdbc.Driver";
                break;
            default:
                System.out.println("其他");
                break;
        }
        keyValueArray.put("url", url);
        keyValueArray.put("className", className);
        return keyValueArray;

    }

    //获取不同数据库数据字典（列相关）
    public static String getSql(String dbType, String tableName) throws Exception {
        String sql = "";
        String owner = tableName.substring(0, tableName.indexOf("."));
        String tableNameNew = tableName.substring(tableName.indexOf(".") + 1);
        if (dbType.equals("mysql")) {
            sql = "\tselect A.COLUMN_NAME,A.ORDINAL_POSITION,A.COLUMN_COMMENT,UPPER(DATA_TYPE) DATA_TYPE," +
                    "CHARACTER_MAXIMUM_LENGTH," + "NUMERIC_PRECISION,NUMERIC_SCALE from information_schema.`COLUMNS` " +
                    "A where TABLE_SCHEMA='" + owner + "' and A.TABLE_NAME='" + tableNameNew + "' order by " +
                    "ORDINAL_POSITION+0";
        }
        if (dbType.equals("gbase")) {
            sql = "select a.column_name, a.column_id as ORDINAL_POSITION, b.comments as COLUMN_COMMENT, a.data_type, " +
                    "a.data_length as CHARACTER_MAXIMUM_LENGTH, a.data_precision as NUMERIC_PRECISION, " +
                    "a.data_scale as NUMERIC_SCALE " +
                    "from original.user_tab_cols a,  original.user_col_comments b  where \n" +
                    "a.table_name = b.table_name and a.column_name = b.column_name and lower(a.table_name) = '"
                    + tableNameNew + "' order by a.column_id";
        }
        if (dbType.equals("oracle")) {
            sql = "SELECT a.column_name,a.column_id ordinal_position,b.comments column_comment,a.data_type," + "a" +
                    ".data_length character_maximum_length,NULL numeric_precision,NULL numeric_scale " + "FROM " +
                    "all_tab_columns a,all_col_comments b WHERE a.OWNER='" + owner + "' AND b.table_name='" + tableNameNew
                    + "' AND b.table_name=a.table_name AND b.column_name=a.column_name and a.owner=b" + ".owner";
        }
        if (dbType.startsWith("postgres") || dbType.equals("gaussDb")) {
            sql = "SELECT A.COLUMN_NAME,(case when b.column_comment is null then '' else  b.column_comment end )" +
                    " column_comment,A.ordinal_position,A.data_type,A.character_maximum_length," + "A" +
                    ".numeric_precision,A.numeric_scale FROM information_schema.COLUMNS A,(\n" + "SELECT b.relname " +
                    "TABLE_NAME,A.attname COLUMN_NAME,col_description (A.attrelid,A.attnum) " + "column_comment,C" +
                    ".nspname FROM pg_attribute A,pg_class b,pg_namespace C WHERE A.attrelid=b.OID " + "AND b" +
                    ".relnamespace=C.OID) b WHERE A.TABLE_NAME=b.TABLE_NAME AND A.COLUMN_NAME=b.COLUMN_NAME AND " +
                    "A.table_schema=b.nspname AND A.table_schema='" + owner.toLowerCase() + "' AND A.TABLE_NAME='"
                    + tableNameNew.toLowerCase() + "'";
        }
        if (dbType.equals("sqlserver")) {
            sql = "SELECT column_name,ordinal_position,column_comment,data_type,character_maximum_length," +
                    "numeric_precision,numeric_scale FROM (\n" + "SELECT table_name=d.name,table_schame=" +
                    "SCHEMA_NAME(d.uid),column_name=CONVERT (VARCHAR (100),a.name),ordinal_position=CONVERT " +
                    "(VARCHAR (100),a.colorder),column_comment=CONVERT (VARCHAR (100),ISNULL(g.[value],''))," +
                    "data_type=CONVERT (VARCHAR (100),b.name),character_maximum_length=CONVERT (VARCHAR (100)," +
                    "COLUMNPROPERTY(a.id,a.name,'PRECISION')),numeric_precision=CONVERT (VARCHAR (100)," + "ISNULL" +
                    "(COLUMNPROPERTY(a.id,a.name,'Scale'),0)),numeric_scale='' FROM syscolumns a LEFT JOIN " +
                    "systypes " + "b ON a.xusertype =b.xusertype INNER JOIN sysobjects d ON a.id =d.id AND d.xtype " +
                    "='U' AND d.name " + "<> 'dtproperties' AND d.name NOT LIKE 'sys%' LEFT JOIN syscomments e ON a" +
                    ".cdefault =e.id " + "LEFT JOIN sys.extended_properties g ON a.id =g.major_id AND a.colid =g" +
                    ".minor_id LEFT JOIN " + "sys.extended_properties f ON d.id =f.major_id AND f.minor_id =0) a " +
                    "WHERE table_schame='" + owner.toLowerCase() + "' " + "AND table_name='" + tableNameNew.toLowerCase()
                    + "' ORDER BY a.ordinal_position;";
        }
        if (dbType.toLowerCase().equals("influxdb")) {
            sql = "select *  from " + tableNameNew + " where  time>now()- 5m  limit 1";
            //表名保持不变
        }
        return sql;
    }


    //列属性转换influx

    public static ArrayList<DbTableBean> columnAttrChange(InfluxDB influxDB, String sql, String dbName) throws Exception {
        ArrayList<DbTableBean> dbTableBeans = new ArrayList<>();
        QueryResult result = influxDB.query(new Query(sql, dbName));
        for (QueryResult.Result r : result.getResults()) {
            for (QueryResult.Series series : r.getSeries()) {
                for (String values : series.getColumns()) {
                    // 输出每一行数据
                    DbTableBean dbTableBean = new DbTableBean();
                    dbTableBean.setColumn_name(values);
                    dbTableBean.setColumn_comment("");
                    dbTableBean.setData_type("varchar");
                    dbTableBean.setColumn_length("4000");
                    dbTableBeans.add(dbTableBean);
                }
            }
        }
        return dbTableBeans;
    }

    //列属性转换其他
    public static ArrayList<DbTableBean> columnAttrChangeQt(String source_dbType, Connection con, String sql,
                                                            String taskType, String dest_dbType) throws Exception {
        ArrayList<DbTableBean> dbTableBeans = new ArrayList<>();
        ResultSet rsMysql = MetaMysql.querySql(con, sql);
        while (rsMysql.next()) {
            DbTableBean dbTableBean = new DbTableBean();
            dbTableBean.setColumn_name(rsMysql.getString("column_name"));
            //列描述空值处理
            if (rsMysql.getString("column_comment") == null || rsMysql.getString("column_comment").equals("")) {
                dbTableBean.setColumn_comment("");
            } else {
                dbTableBean.setColumn_comment(rsMysql.getString("column_comment"));
            }
            dbTableBean.setOrdinal_position(rsMysql.getString("ordinal_position"));
            //类型转换
            dbTableBean.setData_type(changeDateType(rsMysql.getString("data_type"), dest_dbType));
            dbTableBean.setCharacter_maximum_length(rsMysql.getString("character_maximum_length"));
            dbTableBean.setNumeric_scale(rsMysql.getString("numeric_scale"));
            dbTableBean.setNumeric_precision(rsMysql.getString("numeric_precision"));
            //字符串类型长度转换
            dbTableBean.setColumn_length(columnChange(source_dbType,
                    dbTableBean.getData_type(), dbTableBean.getCharacter_maximum_length(), taskType));
            dbTableBeans.add(dbTableBean);
        }
        return dbTableBeans;
    }

    public static String changeDateType(String data_type, String db_type) {
        String dateTypeChange = "";
        //目标库设置
        if (db_type.startsWith("postgres") || db_type.equals("gaussDb") || db_type.equals("mysql")) {
            if (data_type.toUpperCase().equals("CLOB") || data_type.toUpperCase().indexOf("TEXT") >= 0) {
                dateTypeChange = "text";
                return dateTypeChange;
            }
        }
        if (db_type.startsWith("gbase")) {
            if (data_type.toUpperCase().equals("CLOB") || data_type.toUpperCase().indexOf("TEXT") >= 0) {
                dateTypeChange = "varchar";
                return dateTypeChange;
            }
        }
        if (data_type.toUpperCase().lastIndexOf("DATE") != -1 ||
                data_type.toUpperCase().lastIndexOf("CHAR") != -1 ||
                data_type.toUpperCase().lastIndexOf("TIME") != -1) {
            //日期
            dateTypeChange = "varchar";
            return dateTypeChange;

        }
        if (data_type.toUpperCase().lastIndexOf("INT") != -1 ||
                data_type.toUpperCase().lastIndexOf("NUM") != -1 ||
                data_type.toUpperCase().lastIndexOf("DECIMAL") != -1) {
            dateTypeChange = "numeric";
            return dateTypeChange;
        }
        if (dateTypeChange.equals("")) {
            dateTypeChange = "varchar";
            return dateTypeChange;
        }
        return dateTypeChange;
    }

    public static String columnChange(String source_dbType, String data_type, String character_maximum_length,
                                      String taskType) {
        String columnChange = character_maximum_length;
        if (character_maximum_length == null) {
            character_maximum_length = "50";
        }
        if (character_maximum_length.length() > 5) {
            character_maximum_length = "65535";
        }
        if (data_type.toUpperCase().lastIndexOf("CHAR") != -1) {
            if (taskType.equals("stg")) {
                //%char%长度倍数 mysql4倍 oracle3倍  其他1倍
                if (source_dbType.equals("mysql")) {
                    columnChange = Integer.toString(Integer.parseInt(character_maximum_length) * 4);
                }
                if (source_dbType.equals("oracle")) {
                    columnChange = Integer.toString(Integer.parseInt(character_maximum_length) * 3);
                }
                if (source_dbType.startsWith("postgres") || source_dbType.equals("gaussDb")) {
                    columnChange = character_maximum_length;
                }
                //超过4000调为4000
                if (Integer.parseInt(columnChange) > 4000) {
                    columnChange = "4000";
                }
            }
        }
        return columnChange;
    }

    //建表语句拼接
    public static ArrayList<String> concatCreateSql(ArrayList<DbTableBean> dbTableBeans, String tableName,
                                                    String source_dbType,
                                                    ArrayList<String> comments, String taskType, String dest_dbType) throws Exception {

        String sqlCreate = "";
        String sqlStart = "create table " + tableName.toLowerCase() + "(";
        String sqlBody = "";
        String sqlEnd = "";
        ArrayList<String> sqls = new ArrayList<>();
        if (dest_dbType.equals("gaussDb")) {
            sqlEnd = ")WITH (orientation=column, compression=low)\n" + "DISTRIBUTE BY HASH(inner_id) TO GROUP " +
                    "group_version1";
        } else {
            sqlEnd = ")";
        }

        for (int i = 0; i < dbTableBeans.size(); i++) {
            if (dbTableBeans.get(i).getData_type().toUpperCase().lastIndexOf("TEXT") != -1) {
                sqlBody = sqlBody.concat(dbTableBeans.get(i).getColumn_name()).concat(" ")
                        .concat(dbTableBeans.get(i).getData_type()).concat(",\n");
            }
            if (dbTableBeans.get(i).getData_type().toUpperCase().lastIndexOf("CHAR") != -1) {
                sqlBody = sqlBody.concat(dbTableBeans.get(i).getColumn_name()).concat(" ")
                        .concat(dbTableBeans.get(i).getData_type())
                        .concat("(").concat(dbTableBeans.get(i).getColumn_length()
                                .concat(")")).concat("," + "\n");
            }
            if (dbTableBeans.get(i).getData_type().toUpperCase().lastIndexOf("NUMERIC") != -1) {
                sqlBody = sqlBody.concat(dbTableBeans.get(i).getColumn_name()).concat(" ")
                        .concat(dbTableBeans.get(i).getData_type())
                        .concat("(").concat(dbTableBeans.get(i).getNumeric_precision().concat(","))
                        .concat(dbTableBeans.get(i).getNumeric_scale().concat(")")).concat(",\n");
            }
            //comments
            if (dest_dbType.toLowerCase().startsWith("postgres")) {
                String comment = "COMMENT ON COLUMN " + tableName + "." + dbTableBeans.get(i).getColumn_name()
                        + " is '" + dbTableBeans.get(i).getColumn_comment() + "'";
                comments.add(comment);
            }
        }
        String update_time = "";
        if (taskType.equals("stg")) {
            update_time = getDbupdatetime(dest_dbType);
        } else {
            update_time = getDbupdatetime(source_dbType);
        }
        if (taskType.equals("stg")) {
            sqlBody = sqlBody + " inner_id varchar(50),\n" + " inner_time varchar(50) \n";
        } else {
            sqlBody = sqlBody.concat("push_id varchar(50), \n").concat("push_time varchar(50) \n");
        }
        sqlCreate = sqlStart.concat("\n").concat(sqlBody).concat(sqlEnd);
        sqls.add(sqlCreate);
        return sqls;
    }


    public static String getDbuuid(String db_type) throws Exception {
        String uuid = "";
        if (db_type.startsWith("postgres")) {
            uuid = "REPLACE( md5(random()::text || clock_timestamp()::text),'-','')";
        }
        return uuid;
    }

    public static String getDbupdatetime(String db_type) throws Exception {
        String upddate_time = "";
        if (db_type.startsWith("postgres")) {
            upddate_time = "CURRENT_TIMESTAMP(0) ::timestamp without time zone";
        }
        return upddate_time;
    }

    public static String sm2Encrypt(String publicKey, String plainText) throws Exception {
        SMutil sm2 = new SMutil();
        String cipherText = sm2.sm2encrypt(publicKey, plainText);
        return cipherText;
    }

    public static String getColumnsStrClick(Cqlb cqlb, String table_name,
                                            int k, String db_type, InfluxDB influxDb, Connection conn, String cronFun) {
        String selectColumnColSql = "";
        String selectColumnCol = "";
        String selectColumn = "";
        try {

            //生成查询列sql

            selectColumnColSql = "select concat('replace(',column_name,',''\\\\\\\\''',',''-''',')',' as '," +
                    "column_name ) col" +
                    " from information_schema.columns a where a.TABLE_SCHEMA='" + cqlb.getSchema_name() + "'  " +
                    " and table_name='" + table_name + "'";

            ResultSet rsCol;
            rsCol = DBSql.querySql(conn, selectColumnColSql);
            while (rsCol.next()) {
                String selectColumnCol1 = rsCol.getString("col");
                selectColumnCol = selectColumnCol.concat(selectColumnCol1).concat(",");
            }
            if (selectColumnCol.endsWith(",")) {
                selectColumnCol = selectColumnCol.substring(0, selectColumnCol.length() - 1);
            }
            selectColumn = selectColumn.concat(selectColumnCol).concat(" ");  //从源端查询数据的语句
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        return selectColumn;
    }


    //获取源表字段v2.0
    public static String getColumnsStr2(Cqlb cqlb, String db_type, Connection conn) throws Exception {
        String selectColumnColSql = "";
        String selectColumnCol = "";
        String selectColumn = "";
        ResultSet rs = null;
        if (db_type.toLowerCase().startsWith("postgres") || db_type.equals("mysql")) {
            selectColumnColSql = "select column_name col from information_schema.columns a where " +
                    "concat(lower(a.table_schema),'.',lower(a.table_name))='" + cqlb.getFull_table_name() +
                    "' and column_name not in('inner_id','inner_time')";
            rs = DBSql.querySql(conn, selectColumnColSql);
        }
        if (db_type.toLowerCase().startsWith("gbase")) {
            selectColumnColSql = "select column_name col from original.user_tab_columns " +
                    "where lower(table_name) = '" + cqlb.getTable_name() +
                    "' and lower(column_name) not in('inner_id','inner_time')";
            rs = DBSql.querySql(conn, selectColumnColSql);
        }
        while (rs.next()) {
            selectColumnCol = selectColumnCol.concat(rs.getString("col")).concat(",");
        }
        selectColumn = selectColumnCol.substring(0, selectColumnCol.length() - 1);

        return selectColumn;
    }


    public static String getDualSql(String dbType) {
        String sql = "";
        if (dbType.toLowerCase().equals("mysql")) {
            sql = "select 1";
        }
        if (dbType.toLowerCase().equals("influxdb")) {
            sql = "SHOW DATABASES";
        }

        return sql;
    }

    //4.8数据定期清空
    public static void delData(int days, String type, String frequency) throws Exception {
        Connection conn = null;
        try {
            conn = MetaMysql.connect();
            //保留两天数据
            String timeBefore = MyDateUtils.getBeforeDayBydate(MyDateUtils.getDataString(new Date()), days).
                    substring(0, 10);
            String sql = "";

            ArrayList<String> sqls = new ArrayList<>();
            ArrayList<PzDelTable> pzDelTables = new ArrayList<>();

            sql = "select table_name,add_column from pz_del_table where type='" + type + "' and frequency='" + frequency + "'";
            ResultSet rs = MetaMysql.querySql(conn, sql);
            while (rs.next()) {
                PzDelTable pzDelTable = new PzDelTable();
                pzDelTable.setTableName(rs.getString("table_name"));
                pzDelTable.setAdd_column(rs.getString("add_column"));
                pzDelTables.add(pzDelTable);
            }

            for (int i = 0; i < pzDelTables.size(); i++) {
                sql = "delete from " + pzDelTables.get(i).getTableName() + " where " + pzDelTables.get(i).getAdd_column() + "<'" + timeBefore + "'";
                sqls.add(sql);
            }


            for (int i = 0; i < sqls.size(); i++) {
                MetaMysql.execSql(conn, sqls.get(i));
            }
        } finally {
            closeDb(conn);
        }


    }


}
